Release 10.1A: OpenEdge Reporting:
Query/Results Administration and Development

Table relationships and multi-table queries

To build a query that contains information from two or more tables, Results must have a description of how the tables used in the query relate to each other. During the initial build process, Results analyzes the connected databases and defines a default set of relationships between tables in the connected databases.

A default relationship between two tables is based on the following conditions:

A field with the same name and data type specification exists in the two tables.
The field is defined as the only component or the leading component of a unique index in at least one of the tables.

Results stores the default table relationships in the QC7 file following the initial build process.

Default table relationships in Results are called OF relationships in the Progress 4GL. Two tables that meet the conditions stated above can be programmatically joined using the OF option in a Record phrase in a Progress 4GL statement that reads database records. For more information about record access and the Progress 4GL, see the OpenEdge Development: Progress 4GL Handbook .

To display the Table Relationships dialog box where you can view and edit relationships in Results, choose Query Site Admin Table Relationships. The Table Relationships dialog box appears:

The Table Relationships dialog box contains the following user-interface elements:

Base table

Lists the names of all tables available to your Results user ID from the connected databases. Select a table from this list to access information about the relationships defined for the table in Results.

Available to be related

Lists all the tables available to you in Results that are not related to the current base table. Initially, these are the tables that do not meet the OF requirements.

Related tables

Lists all the tables available that are related to the current base table.

Relationship buttons

These buttons and display area perform the following actions:

Add — Adds an unrelated table to the list of related tables. If Results cannot form a default relationship between the base table and the selected table, it prompts you to define a relationship.
Remove — Deletes a highlighted table from the list of related tables. During this process, Results deletes the relationship between the highlighted table and the base table, and places the highlighted table to the list of unrelated tables.
Edit — Edits the existing relationship between the current base table and a highlighted table in the list of related tables.
Relationship — Displays the Progress 4GL syntax of the Record phrase that forms the current table relationship. When you highlight a table from the list of related tables, the syntax for the relationship between the current base table and the highlighted table appears. For more information, see the OpenEdge Development: Progress 4GL Reference .

The OK button confirms all modifications made in the Table Relationships dialog box and writes the modifications to the current QC7 file. The Cancel button cancels all modifications made and exits the Table Relationships dialog box.

The default table relationships that Results creates during the initial build process should provide a strong foundation for most multi-table queries. However, you might want to customize the table relationships available to your users. The Table Relationships dialog box also allows you to:

Create new table relationships that do not adhere to the definition of a default table relationship in Results.
Delete certain default table relationships that provide inappropriate access to sensitive information.
Redefine certain default table relationships that specify selection criteria or prohibit access to sensitive information.
Note: If you change or delete a table relationship, saved queries that depend on that relationship become invalid and no longer function.

Choose Query Site Admin Application Rebuild to rebuild existing Results queries based on modifications to the table relationships. This option also produces information about any existing Results queries that cannot be rebuilt as a result of modifications to table relationships. See the "Database schema changes" section for more information about rebuilding Results queries.

The remainder of this section describes how to use the Table Relationships dialog box to create, edit, and delete table relationships.

Creating a table relationship

You can use the Table Relationship dialog box to create a new table relationship.

To create a new table relationship in Results:

Select a base table for the new relationship. The Table Relationships dialog box displays the current relationship information for that table.
Select a table from the list of unrelated tables.
Choose Add. Results attempts to form a default relationship between the base table and the selected unrelated table.

If Results cannot form a default relationship between the two tables, the Join Construction dialog box appears to help you define the relationship. See the "The Join Construction dialog box" section for information about defining a table relationship using the Join Construction dialog box.

When you finish building the relationship using the Join Construction dialog box and return to the Table Relationships dialog box, the previously unrelated table appears in the list of related tables and the syntax of the new relationship appears in the Relationship field.

Choose OK in the Table Relationships dialog box to write the table relationship to the current QC7 file.

Editing a table relationship

You can also use the Table Relationship dialog box to edit a table relationship.

To edit an existing table relationship in Results:

Select a base table.
Select a table from the list of related tables.
Choose Edit.

The Join Construction dialog box appears. Use it to redefine the relationship between the current base table and the selected related table. See the "The Join Construction dialog box" section for information about defining a table relationship.

When you finish redefining the relationship and return to the Table Relationships dialog box, the syntax of the new relationship appears in the Relationship field.

Choose OK in the Table Relationships dialog box to write the table relationship modifications to the current QC7 file.